﻿using ModelLayer;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DataAccessObjectLayer
{
    public class DBPartReservation : DBClass, IDBPartReservation
    {
        private List<Station> stations;

        /// <summary>
        /// Builds a query with a 'where'-clause.
        /// </summary>
        /// <param name="wClause">The where clause.</param>
        /// <returns></returns>
        protected override string BuildQuery(string wClause)
        {
            var query = "SELECT * FROM PartReservation";
            if (wClause.Length > 0)
            {
                query += " WHERE " + wClause;
            }
            return query;
        }

        /// <summary>
        /// Gets all part reservations in the database.
        /// </summary>
        /// <param name="resvId">The resv identifier.</param>
        /// <returns>PartReservation.</returns>
        public List<PartReservation> GetAllPartReservation(long resvId)
        {
            DBStation dbStation = new DBStation();
            stations = dbStation.GetAllStations();

            string wClause = "FK_ReservationId = " + resvId;
            return MiscWhere(wClause).Cast<PartReservation>().ToList();
        }

        /// <summary>
        /// Gets a part reservation.
        /// </summary>
        /// <param name="partResvId">The part resv identifier.</param>
        /// <returns>PartReservation.</returns>
        public PartReservation GetPartReservation(long partResvId)
        {
            string wClause = "Id = " + partResvId;
            return (PartReservation)SingleWhere(wClause);
        }

        /// <summary>
        /// Inserts a part reservation in the database.
        /// </summary>
        /// <param name="partResv">The part resv.</param>
        /// <param name="resvId">The resv identifier.</param>
        /// <returns>The number of affected rows</returns>
        /// <exception cref="System.Exception">Insert DBPartReservation went wrong</exception>
        public int InsertPartReservation(PartReservation partResv, long resvId)
        {
            int nextId = GetMax.GetMaxId("SELECT MAX(Id) FROM PartReservation");
            nextId = nextId + 1;
            const string query = "INSERT INTO PartReservation(Id, Count, PickupDate, FK_ReservationId, FK_StationId) "
                                 + "VALUES(@Id, @Count, @PickupDate, @FK_ReservationId, @FK_StationId)";

            var dbCon = new DBCon();

            // insert new PartReservation
            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                connection.Open();
                //SqlTransaction transaction;
                //var cmdIns = SqlCommand(connection, out transaction);
                var cmdIns = new SqlCommand { Connection = connection, CommandText = query };

                int rc;
                using (cmdIns)
                {
                    cmdIns.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = nextId });
                    cmdIns.Parameters.Add(new SqlParameter("@Count", SqlDbType.Int) { Value = partResv.Count });
                    cmdIns.Parameters.Add(new SqlParameter("@PickupDate", SqlDbType.DateTime) { Value = partResv.PickupDate });
                    cmdIns.Parameters.Add(new SqlParameter("@FK_ReservationId", SqlDbType.BigInt) { Value = resvId });
                    cmdIns.Parameters.Add(new SqlParameter("@FK_StationId", SqlDbType.BigInt) { Value = partResv.Station.Id });

                    rc = cmdIns.ExecuteNonQuery();
                }
                if (rc == -1)
                    throw new Exception("The parteservation was not inserted");
                return rc;
            }
        }

        /// <summary>
        /// Inserts a part route in the database and associates it to a route.
        /// </summary>
        /// <param name="partReservations">The part reservations.</param>
        /// <param name="nextId">The next identifier.</param>
        /// <param name="con">The con.</param>
        /// <param name="transaction">The transaction.</param>
        public void InsertPartReservations(IEnumerable<PartReservation> partReservations, int nextId, SqlConnection con, SqlTransaction transaction)
        {
            int nextPartId = GetMax.GetMaxId("SELECT MAX(Id) FROM PartReservation");
            nextPartId = nextPartId + 1;
            var cmdText =
                new StringBuilder(
                    "INSERT INTO PartReservation(Id, Count, PickupDate, FK_ReservationId, FK_StationId) VALUES ");
            var args = new List<Tuple<long, int, DateTime, long, long>>();
            foreach (PartReservation partReservation in partReservations)
            {
                int cnt = args.Count;
                if (cnt != 0)
                {
                    cmdText.Append(",");
                }
                cmdText.AppendFormat("(@id{0}, @count{0}, @pickup{0}, @resvId{0}, @stationId{0})", cnt);
                args.Add(new Tuple<long, int, DateTime, long, long>(nextPartId + cnt, partReservation.Count,
                    partReservation.PickupDate, nextId, partReservation.Station.Id));
            }
            if (args.Count != 0)
            {
                using (SqlCommand cmd = new SqlCommand(cmdText.ToString(), con, transaction))
                {
                    for (var i = 0; i != args.Count; i++)
                    {
                        cmd.Parameters.AddWithValue("@id" + i, args[i].Item1);
                        cmd.Parameters.AddWithValue("@count" + i, args[i].Item2);
                        cmd.Parameters.AddWithValue("@pickup" + i, args[i].Item3);
                        cmd.Parameters.AddWithValue("@resvId" + i, args[i].Item4);
                        cmd.Parameters.AddWithValue("@stationId" + i, args[i].Item5);
                    }
                    int result = cmd.ExecuteNonQuery();
                    if (result == -1)
                    {
                        throw new Exception("The partreservation was not inserted");
                    }
                }
            }
        }

        /// <summary>
        /// Updates a part reservation in the database.
        /// </summary>
        /// <param name="partResv">The part resv.</param>
        /// <param name="resvId">The resv identifier.</param>
        /// <returns>The number of affected rows</returns>
        public int UpdatePartReservation(PartReservation partResv, long resvId)
        {
            int rc = -1;
            const string query = "UPDATE PartReservation SET " +
                                 "Count = @Count, " +
                                 "PickupDate = @PickupDate, " +
                                 "FK_ReservationId = @FK_ReservationId, " +
                                 "FK_StationId = @FK_StationId, " +
                                 " WHERE Id = @Id";
            var dbCon = new DBCon();

            // update PartReservation

            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                SqlCommand cmdUpd = SqlCommand(connection, out transaction);
                try
                {
                    cmdUpd.CommandText = query;

                    using (cmdUpd)
                    {
                        cmdUpd.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = partResv.Id });
                        cmdUpd.Parameters.Add(new SqlParameter("@Count", SqlDbType.Int) { Value = partResv.Count });
                        cmdUpd.Parameters.Add(new SqlParameter("@PickupDate", SqlDbType.DateTime) { Value = partResv.PickupDate });
                        cmdUpd.Parameters.Add(new SqlParameter("@FK_ReservationId", SqlDbType.BigInt) { Value = resvId });
                        cmdUpd.Parameters.Add(new SqlParameter("@FK_StationId", SqlDbType.BigInt) { Value = partResv.Station.Id });

                        rc = cmdUpd.ExecuteNonQuery();
                    }
                    if (rc == -1)
                        throw new Exception("The parteservation was not updated");
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    TryRollback(e, transaction, "Update in DBPartReservation");
                }
            }

            return rc;
        }

        /// <summary>
        /// Removes a part reservation in the database.
        /// </summary>
        /// <param name="partResv">The part resv.</param>
        /// <returns>The number of affected rows</returns>
        public int RemovePartReservation(PartReservation partResv)
        {
            int rc = -1;
            const string query = "DELETE FROM PartReservation WHERE Id = @Id";
            var dbCon = new DBCon();

            // delete PartReservation

            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                var cmdDel = SqlCommand(connection, out transaction);
                try
                {
                    cmdDel.CommandText = query;

                    using (cmdDel)
                    {
                        cmdDel.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = partResv.Id });
                        rc = cmdDel.ExecuteNonQuery();
                    }
                    if (rc == -1)
                        throw new Exception("The parteservation was not removed");
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    TryRollback(e, transaction, "Delete in DBPartReservation");
                }
            }

            return rc;
        }

        /// <summary>
        /// Builds an object from either a dataset or a datareader. The bool 'connected' defines if
        /// it is one or the other
        /// </summary>
        /// <param name="dataSetOrDataReader"></param>
        /// <param name="connected"></param>
        /// <returns></returns>
        protected override object BuildObject(object dataSetOrDataReader, bool connected)
        {
            PartReservation partResv = null;
            if (connected)
            {
                var dataReader = dataSetOrDataReader as SqlDataReader;
                IDBStation dbStation = new DBStation();
                if (dataReader != null)
                    partResv = new PartReservation
                    {
                        Id = (long)dataReader["Id"],
                        Count = (int)dataReader["Count"],
                        PickupDate = (DateTime)dataReader["PickupDate"],
                        Station = dbStation.GetStation((long)dataReader["FK_StationId"])
                    };
            }
            else
            {
                var dataRow = dataSetOrDataReader as DataRow;
                if (dataRow != null)
                    partResv = new PartReservation
                    {
                        Id = (long)dataRow["Id"],
                        Count = (int)dataRow["Count"],
                        PickupDate = (DateTime)dataRow["PickupDate"],
                        Station = stations.FirstOrDefault(s => s.Id.Equals((long)dataRow["FK_StationId"])),
                    };
            }
            object o = partResv;
            return o;
        }
    }
}